ORACLE查询带clob字段的表及对应字段名

size pagesize 0

set head on

set wrap on

col TABLE_NAMEfor a50

col column_name for a50

select TABLE_NAME,column_name from user_tab_columns where TABLE_NAME in('ADVISE_INDEX' ,'ADVISE_MQT' ,'ADVISE_PARTITION' ,'ADVISE_TABLE' ,'ADVISE_WORKLOAD' ,'AOP_LOG' ,'BATCH_JOB_EXECUTION' ,'BATCH_JOB_EXECUTION_CONTEXT' ,'BATCH_STEP_EXECUTION' ,'BATCH_STEP_EXECUTION_CONTEXT' ,'DWS_PRESCRIPTION_DETAIL_FORMAL' ,'ETL_RECORD' ,'EXPLAIN_ARGUMENT' ,'EXPLAIN_PREDICATE' ,'EXPLAIN_STATEMENT' ,'EXPLAIN_STREAM' ,'PES_PERSON_PACKAGE' ,'PES_RECEIVE_IMG' ,'PES_RECEIVE_REPORT' ,'PSI_BAS_DRUG' ,'PSI_BAS_DRUG_SPLITPACKING' ,'PSI_OUTP_INJURY_BASE' ,'PSI_OUTP_RCPT_INJURY' ,'PSI_OUTP_REGIST_INJURY' ,'PSI_REG_EMERGENCY' ,'YX_DRUG') AND DATA_TYPE='CLOB' order by TABLE_NAME,column_name;

从db2迁移到oracle需注意varchar2字段类型长度原先大于2000的都会转换为clob字段类型。

db2中查询：

select TABNAME,COLNAME ,LENGTH from syscat.columns where tabschema='CIS' AND LENGTH>2000 and LENGTH< 32672 order by tabname;

需要检查这些表中实际存放的字符长度是否超过了4000字节。再确定在oracle中是使用varchar2(4000)还是clob类型。

SELECT MAX(LENGTH(REMARK)) FROM ETL_RECORD;

在oracle查看特定表和字段对应的类型：

select TABLE_NAME,column_name,DATA_TYPE,data_length from user_tab_columns where TABLE_NAME in('AOP_LOG'  ,'BATCH_JOB_EXECUTION' ,'BATCH_JOB_EXECUTION_CONTEXT' ,'BATCH_STEP_EXECUTION' ,'BATCH_STEP_EXECUTION_CONTEXT' ,'ETL_RECORD' ,'PSI_BAS_DRUG' ,'PSI_BAS_DRUG_SPLITPACKING' ,'PSI_OUTP_INJURY_BASE' ,'PSI_OUTP_RCPT_INJURY' ,'PSI_OUTP_REGIST_INJURY' ,'YX_DRUG') and column_name in('CONTENT' ,'SHORT_CONTEXT' ,'EXIT_MESSAGE' ,'REMARK' ,'INJURY_PROCES' ,'AMOUNT')  order by TABLE_NAME,column_name;

===以下为修改clob为varchar:

alter table    ADVISE_INDEX                       rename column     COLNAMES       TO     COLNAMES_BK    ;                 alter table    ADVISE_INDEX                       add     COLNAMES       VARCHAR2(4000)    ;         update    ADVISE_INDEX                       set     COLNAMES       =    dbms_lob.substr(     COLNAMES_BK    ,4000)    ;                 commit; alter table    ADVISE_INDEX                       drop column     COLNAMES_BK    ;        

===以下为修改varchar为clob：

模拟情景，表：batchintfloadlog，要修改字段：resultinfo，字段 从原来的 varchar2 修改为 clob

1、假设要修改字段数值为空，则可以直接修改；​

可是发现如下错误：​

SQL> alter ​table batchintfloadlog modify  (resultinfo clob); 

alter ​table batchintfloadlog modify (resultinfo clob); 

ORA-22858: 数据类型的更改无效

​经查找资料：可参见：http://www.360doc.com/content/12/0627/10/7662927_220705696.shtml，发现clob类型比较特殊，和其他字段类型不同，不可以从其他字段类型直接转换为clob（blob也一样），可以通过long类型作为中间转换的桥梁，即先将varchar2转换为long，然后再将long转换为clob，即可。

SQL> alter table test modify (loc long );

Table altered

SQL> alter table test modify (loc clob );

Table altered

2、假设要修改字段​有数据，则可以使用以下两种方法；

方法一：​

alter table batchintfloadlog rename column resultinfo to resultinfo_temp;

alter table batchintfloadlog add resultinfo clob;

update batchintfloadlog set resultinfo=trim(resultinfo_temp);

alter table batchintfloadlog drop column resultinfo_temp;

方法二：

create table batchintfloadlog_temp  as select * from batchintfloadlog where 1=2; 

alter table batchintfloadlog_temp modify (resultinfo long); 

alter table batchintfloadlog_temp modify (resultinfo clob); 

insert into batchintfloadlog_temp select * from batchintfloadlog;

drop table batchintfloadlog; 

rename batchintfloadlog_temp to batchintfloadlog;​